IF EXISTS (SELECT*
  FROM sysobjects
  WHERE Id=OBJECT_ID( N'[dbo].[LSInsight$EditSourceTable]' )
		AND OBJECTPROPERTY( Id, N'IsProcedure' )=1)
BEGIN
DROP PROCEDURE [dbo].[LSInsight$EditSourceTable]
END


/****** Object:  StoredProcedure [dbo].[LSInsight$EditSourceTable]    Script Date: 14/11/2022 11:57:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[LSInsight$EditSourceTable]
(
    @SourceTableName Nvarchar(128) = NULL
	,@PrefixedSourceTableName  Nvarchar(128) =  NULL
	,@DeleteRow NVARCHAR(5) 
)
AS
BEGIN
--IF Delete is TRUE and Sourcetable AND PrefixedSourceTableName exists - DELETE reccord
IF  @DeleteRow = 'TRUE'
BEGIN
DELETE FROM [LSInsight$SourceTablesMap] 
WHERE COALESCE([SourceTableName], '1') = COALESCE(@SourceTableName, '1') 
AND (COALESCE(PrefixedSourceTableName, '1') = COALESCE(@PrefixedSourceTableName, '1') OR PrefixedSourceTableName IS NULL)

END
ELSE
-- IF the Sourcetable is missing but PrefixedSourceTableName exist - UPDATE the reccord
IF EXISTS (SELECT [SourceTableName], PrefixedSourceTableName FROM [dbo].[LSInsight$SourceTablesMap] 
WHERE 
[SourceTableName] != @SourceTableName 
AND PrefixedSourceTableName = @PrefixedSourceTableName)
BEGIN
UPDATE [dbo].[LSInsight$SourceTablesMap] SET IncludeTable = 'TRUE', SourceTableName = @SourceTableName
WHERE [SourceTableName] != @SourceTableName 
AND PrefixedSourceTableName = @PrefixedSourceTableName
END
ELSE

-- IF the PrefixedSourceTableName is missing but Sourcetable exist - UPDATE the reccord
IF EXISTS (SELECT [SourceTableName], PrefixedSourceTableName FROM [dbo].[LSInsight$SourceTablesMap] 
WHERE [SourceTableName] = @SourceTableName 
AND PrefixedSourceTableName != @PrefixedSourceTableName
)
BEGIN
UPDATE [dbo].[LSInsight$SourceTablesMap] SET IncludeTable = 'TRUE', PrefixedSourceTableName = @PrefixedSourceTableName
WHERE [SourceTableName] = @SourceTableName 
AND PrefixedSourceTableName != @PrefixedSourceTableName
END
ELSE

--IF Delete is FALSE and Sourcetable OR PrefixedSourceTableName exists but Include is FALSE - Set Include to TRUE
IF EXISTS (SELECT [SourceTableName], PrefixedSourceTableName FROM [dbo].[LSInsight$SourceTablesMap] 
WHERE ([SourceTableName] = @SourceTableName 
AND PrefixedSourceTableName = @PrefixedSourceTableName
AND IncludeTable = 'FALSE')
AND @DeleteRow = 'FALSE')
BEGIN
UPDATE [dbo].[LSInsight$SourceTablesMap] SET IncludeTable = 'TRUE'
Where ([SourceTableName] = @SourceTableName 
AND PrefixedSourceTableName = @PrefixedSourceTableName
AND IncludeTable = 'FALSE')
AND @DeleteRow = 'FALSE'
END

--IF Delete is FALSE and Sourcetable OR PrefixedSourceTableName exists and Include is TRUE - Do Nothing
IF EXISTS (SELECT [SourceTableName], PrefixedSourceTableName FROM [dbo].[LSInsight$SourceTablesMap] 
WHERE ([SourceTableName] = @SourceTableName 
AND PrefixedSourceTableName = @PrefixedSourceTableName
AND IncludeTable = 'TRUE')
AND @DeleteRow = 'FALSE')
BEGIN
Select 1
END

ELSE 
IF  @DeleteRow = 'FALSE'
--Insert New Table mapping
INSERT INTO [dbo].[LSInsight$SourceTablesMap]
           ([SourceTableName]
           ,[PrefixedSourceTableName]
		   ,[IncludeTable])
VALUES (@SourceTableName, @PrefixedSourceTableName, 'TRUE')

END